require "sqlite3"

#Handle the communication with the database
class SongDatabase
  attr_reader :db

  def initialize()
    if !File.exists?("iremotedatabase.db")
    #Create database
      @db = SQLite3::Database.new("iremotedatabase.db")
      sql = <<-ESQL
        CREATE TABLE artist(artistID INTEGER primary key autoincrement, name varchar(200));
        CREATE TABLE album(albumID INTEGER primary key autoincrement, name varchar(255), artistID int);
        CREATE TABLE song(songID INTEGER primary key autoincrement, name varchar(255));
        CREATE TABLE songrel(songID int, albumID int);
      ESQL
      #Execute the sql
      @db.execute_batch(sql)
    else
      @db = SQLite3::Database.new("iremotedatabase.db")
    end
  end

#Insert the song (as well as artist, album) into our database
  def insertSong(song)
    #Try and locate the album in the database
    sql = "SELECT albumID,artistID FROM album WHERE(name = '#{song.album}')"

    rows = @db.get_first_row(sql)

    if rows.length == 0
      #Need to also insert the album and check if we can find the artst as well
      sql = "SELECT artistID FROM artist WHERE(name = '#{song.artist}')"
      rows = @db.get_first_row(sql)

      if rows.length > 0
        #Artist found, save artist id
        artistID = rows[0]
      else
        #No artist found, insert the artist
        sql = "INSERT INTO artist VALUES(0,'#{song.artist}')"
        @db.execute(sql)
        artistID = @db.last_insert_row_id()
      end
      #Insert the album
      sql = "INSERT INTO album VALUES(0,#{song.album},#{artistID}"
      @db.execute(sql)
      #Insert the song
      sql = "INSERT INTO song VALUES"
    else
       albumID = rows[0]
       artistID = rows[1]
     end
    sql = "INSERT INTO song VALUES(0,'#{song.songTitle}')"
    @db.execute(sql)
    songID = @db.last_insert_row_id()
    sql = "INSERT INTO songrel VALUES(#{songID}, #{albumID})"
    @db.execute(sql)
end
